ZK with Spring DAO and JDBC

Author
Andrew Ho, Principal Engineer, Potix Corporation

Date
Jun 8, 2006

Version
Version 2.1.1 or later

(Use the org.zkoss package instead of the com.potix package.)

This article is out of date, please refer to Small_Talks/2008/November/ZK_With_Spring_JPA_And_A_Model-View-Controller_Pattern for more up to date information.

The ZK is the best way to develop your web's user interface. The Spring Framework is the leading full-stack Java/J2EE application framework. Spring framework delivers significant benefits for many projects, reducing development effort and costs while improving test coverage and quality. We give you an brief introduction to bring Spring DAOs and JDBC into your web application. With the Spring framework, you are easy to persistent your data into your database.

The sample project use the eclipse as programming environment and the Tomcat is the web server. The MySQL is the database system to store data. You can see "Develop ZK Applications with Eclipse" to know how to setup the developing environment.

Install Spring

• Download the spring framework.

• Copy spring.jar to $PRJ/WebContent/WEB-INF/lib directory.

Tomcat 5.5 and JDBC Setting

• Copy your JDBC driver to $TOMCAT/common/lib.

• For MySQL, your can get the MySQL connector.

• New a $PRJ/WebContent/META-INF/context.xml file.

  <?xml version="1.0" encoding="UTF-8"?>

  <!-- JDBC -->
  <Context path="/db1" docBase="db1" debug="5" reloadable="true" crossContext="true">
	<Resource name="jdbc/taskdb" username="root" password="passwd"
	url="jdbc:mysql://localhost:3306/taskdb"
	auth="Container" defaultAutoCommit="false"
	driverClassName="com.mysql.jdbc.Driver" maxActive="20"
	timeBetweenEvictionRunsMillis="60000"
	type="javax.sql.DataSource"/>
  </Context>

• In your web.xml, you have to declare a JNDI resource for it, ex. $PRJ/WebContent/WEB-INF/web.xml

  <?xml version="1.0" encoding="UTF-8"?>
  <web-app ...>
  ... 
	<!-- JDBC -->
	<resource-ref>
		<res-ref-name>jdbc/taskdb</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>
  ...

Spring and JDBC

• The spring configuration file is at $PRJ/WebContent/WEB-INF/spring-config.xml.

• The spring supported many ways for getting datasources. Here, we use the org.springframework.jndi.JndiObjectFactoryBean to look up a JNDI datasource.

  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE beans PUBLIC "-//SPRING/DTD BEAN/EN" "http://www.springframework.org/dtd/spring-beans.dtd">

  <beans>
	<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName">
			<value>java:comp/env/jdbc/taskdb</value>
		</property>
	</bean>
  ...
  </beans>

• In your web.xml, you has to use org.springframework.web.context.ContextLoaderListener to load the spring configuration when the server started. Then, you can use WebApplicationContextUtils to get the spring's application context.

  <?xml version="1.0" encoding="UTF-8"?>
  <web-app ...>
  ... 
	<!-- Spring ApplicationContext -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>/WEB-INF/spring-config.xml</param-value>
	</context-param>
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
  ...

Spring DAO and JdbcTemplate

• DAO stands for data access object, which perfectly describes a DAO's role in an application. DAOs exist to provide a means to read & write data to the database. They should expose this functionality through an interface by which the rest of the application will access them.

package com.potix.task;

import java.util.*;

public interface TaskDAO {
public Task insert(Task t) throws Exception;
public Task update(Task t) throws Exception;
public void delete(Task t) throws Exception;
public Task findById(int id) throws Exception;
public List findAll() throws Exception;
}

• The problem of JDBC code

  As everyone knows, your have to clean up resources and handling error is what makes data access robust. The spring template class can make this easy and maintainable.

• Using the JdbcTemplate

  It will clean up your JDBC code by shouldering the burden of resource management and error handling. All a JdbcTemplate needs to do its work is a Datasouce, which makes creating an instance simple enough:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

• And since all of Spring's DAO template classes are thread-safe, we only need one JdbcTemplate instance of each DataSource in our web application. It's the spring configuration and DAO java code sample.

• The spring-config.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING/DTD BEAN/EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
	<property name="jndiName">
		<value>java:comp/env/jdbc/taskdb</value>
	</property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<constructor-arg>
		<ref bean="dataSource"/>
	</constructor-arg>
</bean>
...

• The DAO implementted, TaskDAOImpl.java:

//TaskDAOImpl.java
...

public class TaskDAOImpl implements TaskDAO {
protected JdbcTemplate jdbcTemplate;
protected DataFieldMaxValueIncrementer taskIncer;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
	this.jdbcTemplate = jdbcTemplate;
}
...
}

• Using the JdbcTemplate

• Writing data

It provide an execute(String sql, Object[] params) method that facilities just that. You would use this method in this way:

String sql = "INSERT INTO tasks VALUES(?, ?, ?)";
Object[] params = { t.getId(), t.getTitle(), t.getDescription() };
int[] types = { Types.INTEGER, Types.VARCHAR, Types.VARCHAR};

jdbcTemplate.update(sql, params, types);

• Reading data

As we known in JDBC programming, when we queried the database we had to iterate through the ResultSet. The spring provided RowMapper interface is responsible for mapping a ResultSet row to an object.

//TaskDAOImpl.java
...
protected class TaskMapper implements RowMapper {
	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		Task t = new Task();
		t.setId(rs.getInt("id"));
		t.setTitle(rs.getString("title"));
		t.setDescription(rs.getString("description"));
	
		return t;
	}
}
public List findAll() throws Exception {
	String sql = "SELECT * FROM tasks";
	return jdbcTemplate.query(sql, new TaskMapper());
}
...

Spring Transaction

• Spring has rich support for transaction management, both programming and declarative. The spring AOP was used here and it's easy to integrate the transactional function into your DAOs and you don't have to modify your existed DAOs.

• JDBC transactions

For we are using straight JDBC in web application's persistence, DataSourceTransactionManager will handle transactional boundaries for you. In the spring-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING/DTD BEAN/EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
...

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> 
	<property name="dataSource">
		<ref bean="dataSource"/>
	</property>
</bean>
...

• Declaring transactions

To employ declarative transaction in your web application, you use TransactionProxyFactoryBean. The proxy factory bean is similar to ProxyFactoryBean, except that it has the specific purpose of wrapping methods in transactional contexts.

• Understanding transaction attributes

A transaction attribute is a description of how transaction policies should be applied to a method.

• Propagation behavior:

1. PROPAGATION_REQUIRED - Support a current transaction, create a new one if none exists. This is the most common choice.
2. PROPAGATION_SUPPORTS - Support a current transaction, execute non-transactionally if none exists.
3. PROPAGATION_MANDATORY - Support a current transaction, throw an exception if none exists.
4. PROPAGATION_REQUIRES_NEW - Create a new transaction, suspend the current transaction if one exists.
5. PROPAGATION_NOT_SUPPORTED - Execute non-transactionally, suspend the current transaction if one exists.
6. PROPAGATION_NEVER - Execute non-transactionally, throw an exception if a transaction exists.
7. PROPAGATION_NESTED - Execute within a nested transaction if a current transaction exists, otherwise behave like PROPAGATION_REQUIRED.

• Isolation levels

1. ISOLATION_DEFAULT - Use the default isolation level of the underlying datastore.
2. ISOLATION_READ_COMMITTED - Indicating that dirty reads are prevented; non-repeatable reads and phantom reads can occur.
3. ISOLATION_READ_UNCOMMITTEDL - Indicating that dirty reads, non-repeatable reads and phantom reads can occur.
4. ISOLATION_REPEATABLE_READ - Indicating that dirty reads and non-repeatable reads are prevented; phantom reads can occur.
5. ISOLATION_SERIALIZABLE - Indicating that dirty reads, non-repeatable reads and phantom reads are prevented.

• Read-only hints

• The transaction timeout period

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING/DTD BEAN/EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
...	
<bean id="taskDAOTarget" class="com.potix.task.TaskDAOImpl">
	<property name="jdbcTemplate">
		<ref bean="jdbcTemplate"/>
	</property>
	<property name="taskIncer">
		<ref bean="taskIncer"/>
	</property>
</bean>

<bean id="taskDAO" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"> 
	<property name="transactionManager"> 
		<ref bean="transactionManager"/>
	</property> 
	<property name="target"> 
		<ref bean="taskDAOTarget"/>
	</property> 
	<property name="transactionAttributes">
		<props> 
			<prop key="insert*">PROPAGATION_REQUIRED, ISOLATION_READ_COMMITTED</prop>
			<prop key="update*">PROPAGATION_REQUIRED, ISOLATION_READ_COMMITTED</prop> 
			<prop key="delete*">PROPAGATION_REQUIRED, ISOLATION_READ_COMMITTED</prop>
			<prop key="find*">PROPAGATION_REQUIRED, ISOLATION_READ_COMMITTED, readOnly</prop> 
		</props> 
	</property>
</bean>
...

ZK with Spring DAO

• Setup ZK

You can see it in details from [Develop ZK Applications with Eclipse Develop ZK Applications with Eclipse].

• Zk with Spring DAO

• The good design of the spring DAO philosophy. The spring DAO isolated a tier for data persistent. You can use the Spring DAO like this:

  //spring bean for "taskDAO"
  ApplicationContext ctx = 
	WebApplicationContextUtils.getRequiredWebApplicationContext(
	(ServletContext)getDesktop().getWebApp().getNativeContext());
  taskDAO = (TaskDAO)ctx.getBean("taskDAO");

  //
  Task t = new Task();
  t.setTitle("My Job1");
  t.setDescription("ZK presentation slides.");

  t = taskDAO.insert(t);

• In your zul, you can store your data into database used the Spring DAO too. In the sample project, a user clicked the "OK" button and the onOK() will be trigged and you can use the Spring DAO to persistent your data into database.

  //task.zul

  <?xml version="1.0" encoding="utf-8"?>
  <?page title="Task"?>
  <window id="taskWnd" title="Task" border="normal" width="300px" 
	use="com.potix.task.TaskWnd" task="${arg.task}">
	<vbox>
		...
		<hbox>
			<button label="OK" onClick="taskWnd.onOK()"/>
			<button label="Cancle" onClick="taskWnd.onCancle()"/>
		</hbox>
	</vbox>
  </window>

  //TaskWnd.java

  public class TaskWnd extends Window {
	...
	
	public void onOK() throws Exception {
		if (task == null) {
			//new
			task = new Task();
			
			Textbox ctrl = (Textbox) this.getFellow("title"); 
			task.setTitle(ctrl.getValue());
			ctrl = (Textbox) this.getFellow("description");
			task.setDescription(ctrl.getValue());
			
			taskDAO.insert(task);
		} else {
			//update
			Textbox ctrl = (Textbox) this.getFellow("title"); 
			task.setTitle(ctrl.getValue());
			ctrl = (Textbox) this.getFellow("description");
			task.setDescription(ctrl.getValue());
			
			taskDAO.update(task);
		}
		
		this.setAttribute("OK", Boolean.TRUE);
		this.detach();
	}
	
	...
  }

• For we need ServetContext java class to find the spring's ApplicationContext, you had to add TOMCAT's servlet-api.jar into your project build path.

Sample

• You can download the eclipse project file, task.zip.
• Unpack it and open eclipse and do File/Import.../Existing Projects into Workspace.
• This schema is at task/WebContent/task.sql and it's for MySQL database.
• The taskList.zul will list all of tasks and you can add, delete or update a task. The task.zul will bring you a dialog to create or modify a task. The TaskListWnd.java and TaskWnd.java are the above two zuls processing code for your business logic to use the TaskDAO to store data into database.
• The "taskIncer" bean used MySQLMaxValueIncrementer to generate the of value of "id" field in the "tasks" table.
• The sample web application:

Reference

1. Develop ZK Applications with Eclipse

2. Spring in Action by Craig Walls and Ryan Breidenbach, see Chap. 1 ~ 5

3. Spring DAO with JDBC, Eclipse, PostgreSQL, MySql

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.